package com.scalar.chiptrack.operations.process;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts.upload.FormFile;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.SqlQueryMappings;


/**
** Upload a specified document to specified path
**
** @author Mallikarjun Ankati
** @params none
**/

public class Upload {

	private Upload(){
	}

	/**
	** Upload the procedure document and Insert into the Procedure_Doc_Table
	**
	** @param processStepType
	** @param procedureDocNumber
	** @param file
	**
	** @return procedureDocID;
	**/

	public static int getProcedureDocID (DBTransaction dbTransaction, String processStepType, String procedureDocNumber, FormFile file) throws ScalarException{

		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="procedure_doc"+"/";
		String readQuery = SqlQueryMappings.get("operations.process.upload.readProcedureDocID");
		int id = readExistingRecord(procedureDocNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

            if(file != null){
                   loadFile(fileLocation, file);
            }

		String query = SqlQueryMappings.get("operations.process.upload.insertProcedureDoc");

		return insertIntoTable (dbTransaction, fileLocation, procedureDocNumber, file, query);
	}

        public static int getProgramDocID (DBTransaction dbTransaction, String processStepType, String programDocNumber, FormFile file) throws ScalarException{
		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="program_doc"+"/";

		String readQuery = SqlQueryMappings.get("operations.process.upload.readProgramDocID");
		int id = readExistingRecord(programDocNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

		if(file != null){
                    loadFile(fileLocation, file);
            }

		String query = SqlQueryMappings.get("operations.process.upload.insertProgramDoc");
			return insertIntoTable (dbTransaction, fileLocation, programDocNumber, file, query);
	}

	public static int getMaskDocID (DBTransaction dbTransaction, String processStepType, String MaskNumber, FormFile file) throws ScalarException{

		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="mask_doc"+"/";

		String readQuery = SqlQueryMappings.get("operations.process.upload.readMaskID");

		int id = readExistingRecord(MaskNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

		if(file != null){
                    loadFile(fileLocation, file);
            }

		String query = SqlQueryMappings.get("operations.process.upload.insertMaskDoc");

		return insertIntoTable (dbTransaction, fileLocation, MaskNumber, file, query);
	}

	public static int getBondingDiagramDocID (DBTransaction dbTransaction, String processStepType, String bondingDiagramNumber, FormFile file) throws ScalarException{

		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="bonding_doc"+"/";

		String readQuery = SqlQueryMappings.get("operations.process.upload.readBondingDocID");
		int id = readExistingRecord(bondingDiagramNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

		if(file != null){
                    loadFile(fileLocation, file);
                }

		String query = SqlQueryMappings.get("operations.process.upload.insertBondingDiagramDoc");

		return insertIntoTable (dbTransaction, fileLocation, bondingDiagramNumber, file, query);
	}

	public static int getMarkingDiagramDocID (DBTransaction dbTransaction, String processStepType, String markingDiagramNumber, FormFile file) throws ScalarException{

		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="marking_diagram_doc"+"/";

		String readQuery = SqlQueryMappings.get("operations.process.upload.readMarkingDiagID");
		int id = readExistingRecord(markingDiagramNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

		if(file != null){
                    loadFile(fileLocation, file);
                }

		String query = SqlQueryMappings.get("operations.process.upload.insertMarkingDiagramDoc");

		return insertIntoTable (dbTransaction, fileLocation, markingDiagramNumber, file, query);
	}


	public static int getMarkingSpecDocID (DBTransaction dbTransaction, String processStepType, String markingSpecNumber, FormFile file) throws ScalarException{

		String fileLocation = ConfigManager.getProcessDocFilePath();
		fileLocation += "/"+processStepType+"/";
		fileLocation +="marking_spec_doc"+"/";

		String readQuery = SqlQueryMappings.get("operations.process.upload.readMarkingSpecID");
		int id = readExistingRecord(markingSpecNumber, readQuery);

		if(processStepType.equals("")){
			return id;
		}else{
			if(id != 0){ return id; }
		}

		if(file != null){
                    loadFile(fileLocation, file);
                }

		String query = SqlQueryMappings.get("operations.process.upload.insertMarkingSpecDoc");

		return insertIntoTable (dbTransaction, fileLocation, markingSpecNumber, file, query);
	}


	/*
	These 2 methods are added by Bhaskar on 17 Feb 2004 inorder to upload the 2 files from Board Data Entry screen.
	*/
	public static int getBomRevNoId (DBTransaction dbTransaction, String processStepType, String bomRevNumber, String doc_id, FormFile file) 
		throws ScalarException
	{
        String fileLocation = ConfigManager.getProcessDocFilePath();
		int id = 0;
		fileLocation += "/"+processStepType+"/";
		fileLocation +="bomRevNo_doc"+"/";
		String readQuery = "";
		if ( doc_id.equals ("-1") && file.getFileSize() != 0 )
		{
            loadFile(fileLocation, file);
            String query = SqlQueryMappings.get("operations.process.upload.insertBomRevNoDoc");
            return insertIntoTable (dbTransaction, fileLocation, bomRevNumber, file, query);
		}
		else if ( !doc_id.equals("-1") && file.getFileSize() != 0 )
		{
			readQuery = SqlQueryMappings.get("operations.process.upload.readBomRevNoDocID");
			id = readExistingRecord ( dbTransaction, bomRevNumber, readQuery, fileLocation, file.getFileName(), doc_id );
			if ( id == -1 )
			{
				if ( file != null )
				{
					loadFile ( fileLocation, file );
				}
				return updateIntoTable ( dbTransaction, fileLocation, bomRevNumber, file, doc_id );
			}
		}
        else if( !doc_id.equals("-1") && file.getFileSize() == 0  )
        {
            updateBomRevNumber( dbTransaction, bomRevNumber, doc_id );
            return Integer.parseInt ( doc_id );
        }

		return Integer.parseInt ( doc_id );
	}

    public static int getSchematicId ( DBTransaction dbTransaction, String processStepType, String schematicNumber, String doc_id, FormFile file)
		throws ScalarException
	{
		String fileLocation = ConfigManager.getProcessDocFilePath();
		int id = 0;
		fileLocation += "/"+processStepType+"/";
		fileLocation +="schematic_doc"+"/";
		String readQuery = "";
		if ( doc_id.equals ("-1") && file.getFileSize() != 0 )
		{
			loadFile(fileLocation, file);
			String query = SqlQueryMappings.get("operations.process.upload.insertSchematicDoc");
			return insertIntoTable (dbTransaction, fileLocation, schematicNumber, file, query);
		}
        else if ( !doc_id.equals("-1") && file.getFileSize() != 0 )
		{
			readQuery = SqlQueryMappings.get("operations.process.upload.readSchematicDocID");
			id = readExistingRecord ( dbTransaction, schematicNumber, readQuery, fileLocation, file.getFileName(), doc_id );
			if ( id == -1 )
			{
				if ( file != null )
				{
					loadFile ( fileLocation, file );
				}
				return updateIntoTable ( dbTransaction, fileLocation, schematicNumber, file, doc_id );
			}
		}
        else if( !doc_id.equals("-1") && file.getFileSize() == 0  )
        {
            updateSchematicRevNumber( dbTransaction, schematicNumber, doc_id );
            return Integer.parseInt ( doc_id );
        }
		return Integer.parseInt ( doc_id );
	}

    public static void  updateBomRevNumber ( DBTransaction dbTransaction, String bomNumber, String doc_id )
	throws ScalarException
	{
        Connection conn = null;
        PreparedStatement ps = null;
        boolean status = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.upload.updateBomDocNumber"));
            //ps = conn.prepareStatement( "Update Doc_table set Doc_Number = ? where Doc_Id = ?" );
            ps.setString( 1, bomNumber );
            ps.setInt   ( 2, Integer.parseInt( doc_id ) );

            ps.executeUpdate();
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Could not update Bom Rev Number", sql);
		}
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) {}
            }
            if( ps != null )
            {
                try{ ps.close(); }catch(Exception e){}
			}
			if( conn != null )
            {
                try{ conn.close(); }catch(Exception e){}
			}
		}
    }

    public static void  updateSchematicRevNumber ( DBTransaction dbTransaction, String schematicNumber, String doc_id )
    throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean status = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.upload.updateSchematicDocNumber"));
            //ps = conn.prepareStatement( "Update Doc_table set Doc_Number = ? where Doc_Id = ?" );
            ps.setString( 1, schematicNumber );
            ps.setInt   ( 2, Integer.parseInt( doc_id ) );

            ps.executeUpdate();
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Could not update Schematic Number", sql);
		}
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) {}
            }
            if( ps != null )
            {
                try{ ps.close(); }catch(Exception e){}
			}
			if( conn != null )
            {
                try{ conn.close(); }catch(Exception e){}
			}
		}
    }

	public static void loadFile( String fileLocation, FormFile file) throws ScalarException
    {
        try
        {
            String fileName = file.getFileName();
            if ((fileName == null) || (fileName.trim().length() <= 0))
            {
                return;
            }

			File tempFile = new File(fileLocation);

			if( !tempFile.exists() )
            {
				if( !tempFile.mkdirs() )
                {
					throw new ScalarException("Could not create the directory : "+fileLocation);
				}
			}

			InputStream stream = file.getInputStream();
			OutputStream bos = new FileOutputStream(fileLocation+file.getFileName());
			int bytesRead = 0;
			byte[] buffer = new byte[8192];

			while ((bytesRead = stream.read(buffer, 0, 8192)) != -1)
            {
				bos.write(buffer, 0, bytesRead);
			}

			bos.close();

		}
        catch(FileNotFoundException fnfe)
        {
			fnfe.printStackTrace();
			throw new ScalarException(" Specified file : "+file.getFileName(), fnfe);
		}
        catch(IOException ioe)
        {
			ioe.printStackTrace();
			throw new ScalarException(" Streaming is failed : "+file.getFileName(), ioe);
		}
	}

    public static void fileAsSave( String sourceFileNameWithCompletePath, String targetFileNameWithCompletePath ) throws ScalarException
    {
        try
        {
            if ( ( sourceFileNameWithCompletePath == null ) || ( sourceFileNameWithCompletePath.trim().length() <= 0 ) )
            {
                return;
            }
            if ( ( targetFileNameWithCompletePath == null ) || ( targetFileNameWithCompletePath.trim().length() <= 0 ) )
            {
                return;
            }

            File file = new File( sourceFileNameWithCompletePath );
            if( file == null || file.isFile() == false)
            {
                return;
            }
            InputStream stream  = new FileInputStream( file );
			OutputStream bos    = new FileOutputStream( targetFileNameWithCompletePath );
			int bytesRead = 0;
			byte[] buffer = new byte[8192];

			while ( ( bytesRead = stream.read( buffer, 0, 8192 ) ) != -1 )
            {
				bos.write( buffer, 0, bytesRead );
			}
            stream.close();
            bos.close();
        }
        catch(FileNotFoundException fnfe)
        {
			fnfe.printStackTrace();
			throw new ScalarException(" Specified file : " + sourceFileNameWithCompletePath, fnfe);
		}
        catch(IOException ioe)
        {
			ioe.printStackTrace();
			throw new ScalarException(" Streaming is failed : " + sourceFileNameWithCompletePath, ioe);
		}
	}

    public static int insertIntoTable (DBTransaction dbTransaction, String fileLocation, String id, FormFile file, String query) throws ScalarException{

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int docID = 0;
		boolean status = false;

		try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement(query);
            ps.setString(1, id);

            if( file != null )
            {
                ps.setString( 2, file.getFileName() );
            }
            else
            {
                ps.setString( 2, "" );
            }

            ps.setString(3, fileLocation);
            ps.executeUpdate();

            ps.close();
            ps = null;
            status = true;

            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.upload.readIdentity"));
            rs = ps.executeQuery();

            if( rs.next() != false )
            {
                docID = rs.getInt( "docID" );
            }

		}
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(file.getFileName()+" upload failed", sql);
		}
        finally
        {
            if( file != null )
            {
                file.destroy();
			}

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if( ps != null )
            {
                try{ ps.close(); }catch(Exception e){}
			}
			if( conn != null )
            {
                try{ conn.close(); }catch(Exception e){}
			}
		}
		return docID;
	}

	public static int readExistingRecord(String docNumber, String query) throws ScalarException{

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int id = 0;

		try{
			conn = DataSource.getConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, docNumber);
			rs = ps.executeQuery();

			if(rs.next() != false){
				id= rs.getInt("docID");
			}

		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException((query+" ...... failed"), sql);

		}finally{

			if(ps != null){
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
		return id;
	}

	//Bhaskar on 18 Feb overloaded this method inorder to support DBTransaction.
	public static int readExistingRecord ( DBTransaction dbTransaction, String docNumber, String query, String fileLocation,
		String fileName, String doc_id ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int id = 0;

        try
		{
            if (dbTransaction == null)
			{
                conn = DataSource.getConnection();
            }
            else
			{
				conn = dbTransaction.getConnection();
            }
			ps = conn.prepareStatement(query);
			ps.setInt ( 1, Integer.parseInt ( doc_id ) );
			rs = ps.executeQuery();

			if ( rs.next () )
			{
				String doc_number = rs.getString ( "doc_number" );
				String file_Location = rs.getString ( "dt_filelocation" );
				String file_name = rs.getString ( "dt_filename" );
				if ( !doc_number.equals ( docNumber ) || !file_Location.equals ( fileLocation ) || !file_name.equals ( fileName ) )
				{
					id = -1;
				}
				else
				{
					id = Integer.parseInt ( doc_id );
				}
			}
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException((query+" ...... failed"), sql);
		}
		finally
		{
			if(rs != null){
				try{ rs.close(); }catch(Exception e){}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
		return id;
	}

	public static int updateIntoTable ( DBTransaction dbTransaction, String fileLocation, String document_number, FormFile file, String doc_id )
		throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;

		try
		{
			// Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.process.upload.updateFileDetails" ) );
			pstmt.setString ( 1, document_number );

			if(file != null)
			{
				pstmt.setString ( 2, file.getFileName());
			}
			else
			{
				pstmt.setString ( 2, "");
			}
			pstmt.setString ( 3, fileLocation);
			pstmt.setInt ( 4, Integer.parseInt ( doc_id ) );

			pstmt.executeUpdate();
			status = true;
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException(file.getFileName()+ "upload failed", sql);
		}
		finally
		{
			if(file != null)
			{
				file.destroy();
			}
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try {
					if (conn != null) {
						if (status == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if(pstmt != null)
			{
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(Exception e){}
			}
		}
		return Integer.parseInt ( doc_id );
	}


}